 |
|
Oracle Tips by
Burleson |
The Data Dictionary -
Finding Data in the Database
Since we have the pubs.ppt slide, it is easy to
see what each table in our schema consists of. If we didn’t
have the slide, we could describe the tables to find out what makes
up the tables. But what if we didn’t know which tables are in
the PUBS schema? Oracle
provides views that allow us to query this information from the
database.
In Oracle, a view is a pseudo-table that is
created when a query is run against it. In other words, there
is no table called user_tables
. When I query my table_names from user_tables, Oracle temporarily
creates the table to answer the query. There are three levels
of views:
The
View - The user view
will return those items that you own. Your tables, indexes,
sequences etc.
The
View – The all view
will return those objects that you own and those objects that you
have been granted rights on. If another schema has granted
select on one of their tables
to you (the PUBS user), then it will appear in the
all views. It will not appear in the user view.
The
View – The dba view
returns all objects in the database.
If an object exists but
is not returned in the view you use, the database returns an “object
does not exists” error. This is a security feature, because if
you are not granted access to it, you are not allowed to know it
exists.
To get a list of the PUBS tables, we query the
user_tables
view.
SQL> desc user_tables;
Name Null?
Type
--------------------------------- --------
------------TABLE_NAME
NOT NULL
VARCHAR2(30)
TABLESPACE_NAME
VARCHAR2(30)
CLUSTER_NAME
VARCHAR2(30)
IOT_NAME
VARCHAR2(30)
. . .
We are looking
for the table names.
SQL>
SELECT
2 table_name
3
FROM
4
user_tables;
TABLE_NAME
------------------------------
AUTHOR
EMP
JOB
PUBLISHER
SALES
STORE
BOOK_AUTHOR
BOOK
To find all the tables we have access to, use the
all view. The
difference between the user_tables
view and the all_tables/dba_tables view is the
addition of the owner
column. Since the user
view only shows us our own tables, there is no need for the owner column. Now, select the
table_name from the all_tables view. Wow! I
got 1515 tables (you may get a different number depending on the
features you installed in the database). If I query the table
names from the dba_tables
view, I get the same number. This is because we granted the
user PUBS the DBA role.
Since a DBA has access to all objects in the database, the all_tables and dba_tables are the
same.
There are many user/all/dba views, and we will
introduce more of them as we progress. Almost all objects have
a view: user_indexes,
user_sequences, etc.
The above book excerpt is from:
Easy Oracle
SQL
Get Started Fast
writing SQL Reports with SQL*Plus
ISBN
0-9727513-7-8
Col. John Germany
http://www.rampant-books.com/book_2005_1_easy_sql.htm | 
